//
//  JKSQLiteManager.swift
//  ZhiHuDaily-Swift
//
//  Created by albert on 2018/3/5.
//  Copyright © 2018年 安永博. All rights reserved.
//

import UIKit
import FMDB
import Alamofire
import Kingfisher
import JKAlertX

class JKSQLiteManager: NSObject {
    
    // MARK: - 单例

    static let shared = JKSQLiteManager()
    
    static var _storyIDIndex = 0
    static var _IDs = [String]()
    static var _progressText = ""
    
    public let reach: Reachability = Reachability(hostName: "www.baidu.com")
    
    // MARK: - 属性

    /** 数据库队列对象（使用数据库队列可以保证线程安全） */
    var dbQueue: FMDatabaseQueue?
    
    /** 缓存完成的complete */
    var cacheCompletedBlock: ((_ progressText: String) -> ())?
    
    
    // MARK: - 对象方法

    /** 打开数据库 */
    public func openDB(dbName: String) {
        
        // 3.1获取数据库文件存放的路径
        guard let path = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true).last else { return }
        
        let filePath = (path as NSString).appendingPathComponent(dbName)
        
        JKLog(message: filePath)
        
        // 3.2创建数据库
        self.dbQueue = FMDatabaseQueue(path: filePath)
        
        // 3.3创建表
        createTable()
    }
    
    /** 创建表 */
    private func createTable() {
        
        // 1.拼接创建表的SQL语句
        let createTableSQL = "CREATE TABLE IF NOT EXISTS t_story ('storyID' TEXT NOT NULL PRIMARY KEY, 'title' TEXT, 'isCollected' TEXT, 'isLiked' TEXT, 'storyDetail' TEXT, 'createTime' TEXT DEFAULT (datetime('now', 'localtime')));";
        
        let createMoreListTableSQL = "CREATE TABLE IF NOT EXISTS t_storyList ('ID' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 'storyID' TEXT, 'title' TEXT, 'date' TEXT, 'isRead' TEXT, 'isTop' TEXT, 'isLatest' TEXT, 'isCollected' TEXT, 'list' TEXT, 'updateTime' TEXT, 'createTime' TEXT DEFAULT (datetime('now', 'localtime')));";
        
        // 2.执行SQL语句
        self.dbQueue?.inDatabase({ (db) in
            
            if db.executeUpdate(createTableSQL, withArgumentsIn: []) {
                JKLog(message: "创建表成功")
            }
            
            if db.executeUpdate(createMoreListTableSQL, withArgumentsIn: []) {
                JKLog(message: "创建表成功")
            }
        })
    }
    
    // MARK: - 离线下载
    
    /** 离线下载 */
    public func downLoadToLocal() {
        
        if (self.reach.currentReachabilityStatus() == NotReachable) {
            
            JKProgressHUD.showError(withStatus: "您的网络貌似有点问题哦~")
            
            return
        }
        
        if (self.reach.currentReachabilityStatus() == ReachableViaWWAN) {
            
            let _ = JKAlertView(title: "提示", message: "当前处于移动网络，确定要离线下载吗？", style: JKAlertStylePlain).addAction(JKAlertAction(title: "还是算了吧~~", style: JKAlertActionStyleDefault, handler: { (action) in
                
                
            }))?.addAction(JKAlertAction(title: "流量多，不怕！", style: JKAlertActionStyleDefault, handler: { (action) in
                
                self.startDownLoad()
                
            }))?.show()
            
            return
        }
        
        startDownLoad()
    }
    
    /** 开始离线 */
    private func startDownLoad() {
        
        JKNewsListGetDataModel.loadLatestData { (dict, error) in
            
            if error != nil {
                
                return
            }
            
            if dict == nil { return }
            
            guard let topStoryDicts    = dict![JKTopStoryListKey] as? NSArray else { return }
            guard let latesStorytDicts = dict![JKStoryListDictKey] as? NSArray else { return }
            
            let topIDs = topStoryDicts.value(forKeyPath: "id") as! [AnyObject]
            let latestIDs = latesStorytDicts.value(forKeyPath: "id") as! [AnyObject]
            
            var topIds = [String]()
            var latestIds = [String]()
            
            for obj in topIDs {
                
                topIds.append(String(describing: obj))
            }
            
            for obj in latestIDs {
                
                latestIds.append(String(describing: obj))
            }
            
            JKSQLiteManager.cacheDetailWithIDs(IDs: topIds + latestIds)
        }
    }
    
    // MARK: - 类方法
    
    /** 缓存css文件 */
    public class func cacheStoryDetailCss(ID: String?, cssUrlStr: String?) {
        
        if ID == nil || cssUrlStr == nil {
            return
        }
        
        let cssPath = (JKCssDirectory as NSString).appendingPathComponent(ID! + ".css")
        
        // 已缓存，直接返回
        if FileManager.default.fileExists(atPath: cssPath) {
            
            return
        }
        
        // 判断css缓存文件夹路径是否存在
        if !FileManager.default.fileExists(atPath: JKCssDirectory) {
            
            do {
                try FileManager.default.createDirectory(atPath: JKCssDirectory, withIntermediateDirectories: true, attributes: nil)
                
            }catch{
                
                JKLog(message: error)
                
                return
            }
        }
        
        // 创建URL
        guard let url = URL(string: cssUrlStr!) else { return }
        
        // 创建URLRequest
        let reuqest = URLRequest(url: url)
        
        // 设置下载路径
        let destination: DownloadRequest.DownloadFileDestination = { _, _ in
            
            let fileUrl = URL(fileURLWithPath: cssPath)
            
            return (fileUrl, [.createIntermediateDirectories, .removePreviousFile])
        }
        
        // 开始下载
        Alamofire.download(reuqest, to: destination).response { response in // method defaults to `.get`
            
            JKLog(message: response.request)
            JKLog(message: response.response)
            JKLog(message: response.temporaryURL)
            JKLog(message: response.destinationURL)
            JKLog(message: response.error)
        }
    }
    
    
    // MARK: - ------------------增------------------
    
    /** 缓存顶部topStories */
    public class func cacheTopStories(topStories: [[String : AnyObject]]?, date: String?) {
        
        if topStories == nil || date == nil {
            return
        }
        
        guard let ids = (topStories! as NSArray).value(forKeyPath: "id") as? [AnyObject] else {
            
            return
        }
        
        let arr = ids.sorted { (obj1, obj2) -> Bool in
            
            let result = String(describing: obj1).compare(String(describing: obj2))
            
            return result == ComparisonResult.orderedDescending
        }
        
        // 从数据库取得的topStories的ID列表
        let topIDs = JKSQLiteManager.queryTopStoryIDs()
        
        // 比较两个列表是否完全相同，是的话直接返回，不需要进行缓存
        if (topIDs.count == arr.count) {
            
            var allEqual = true
            
            for i in 0 ..< arr.count {
                
                if String(describing: arr[i]) == topIDs[i] { continue }
                
                allEqual = false
            }
            
            if allEqual { return }
        }
        
        // 如果有不相同的，删除全部topStories，重新缓存
        let deleteSQL = "delete from t_storyList where isTop = '1';"
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
            
            db.executeUpdate(deleteSQL, withArgumentsIn: [])
        })
        
        for dict in topStories!.reversed() {
            
            guard let storyID = dict["id"] else { return }
            guard let title = dict["title"] else { return }
            
            // 将字典转为NSData
            guard let listData = try? JSONSerialization.data(withJSONObject: dict, options: JSONSerialization.WritingOptions.prettyPrinted) else {
                return
            }
            
            // 将NSData转为字符串
            guard let listStr = String(data: listData, encoding: String.Encoding.utf8) else { return }
            
            // 拼接插入SQL语句
            let insertSQL = "INSERT OR IGNORE INTO t_storyList (storyID, title, isCollected, isRead, isTop, isLatest, list, date, updateTime) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);"
            
            JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
                
                db.executeUpdate(insertSQL, withArgumentsIn: [storyID, title, "0", "0", "1", "1", listStr, date!, "0"])
            })
        }
    }
    
    /** 缓存首页数据 */
    public class func cacheStoryLists(stories: [[String : AnyObject]]?, date: String?, isTop: Bool, isLatest: Bool, isMore: Bool) {
        
        if stories == nil {
            return
        }
        
        var dict = [String : AnyObject]()
        
        for i in (0 ..< stories!.count).reversed() {
            
            dict = stories![i]
            
            let storyID = String(format: "%@", dict["id"] as! CVarArg)
            let title = dict["title"] as! String
            
            let datestr = (date == nil) ? (dict["date"] as! String) : date
            
            // 将字典转为NSData
            guard let listData = try? JSONSerialization.data(withJSONObject: dict, options: JSONSerialization.WritingOptions.prettyPrinted) else {
                return
            }
            
            // 将NSData转为字符串
            guard let listStr = String(data: listData, encoding: String.Encoding.utf8) else { return }
            
            // 拼接插入SQL语句
            let insertSQL = "INSERT INTO t_storyList (storyID, title, isCollected, isRead, isTop, isLatest, list, date, updateTime) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);"
            
            let querySQL = "SELECT * FROM t_storyList WHERE storyID = '" + storyID + "' and isTop = '0';"
            
            if (isMore) {
                
                updateNotLatestWithStoryID(storyID: storyID)
            }
            
            JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
                
                guard let result = db.executeQuery(querySQL, withArgumentsIn: []) else { return }
                
                if result.next() {
                    
                    result.close()
                    
                    return
                }
                
                db.executeUpdate(insertSQL, withArgumentsIn: [storyID, title, "0", "0", (isTop ? "1" : "0"), (isLatest ? "1" : "0"), listStr, datestr!, "0"])
            })
        }
    }

    /** 批量缓存详情数据 */
    public class func cacheDetailWithIDs(IDs: [String]?) {
        
        guard let _ = IDs else {
            return
        }
        
        _IDs = IDs!
        
        let ID = IDs![_storyIDIndex]
        
        JKNewsListGetDataModel.loadDetailDataWithID(ID: ID, detailBlock: { (detailModel, json, error) in
            
            if error != nil || json == nil {
                
                return
            }
            
            cacheDetail(detailDict: json, ID: ID, justCache: true)
            
            _storyIDIndex += 1
            
            _progressText = String(format: "%.f%%", CGFloat(_storyIDIndex) * 100.0 / CGFloat(_IDs.count))
            
            if JKSQLiteManager.shared.cacheCompletedBlock != nil {
                
                JKSQLiteManager.shared.cacheCompletedBlock!(_progressText)
            }
            
            if (_storyIDIndex == _IDs.count) {
                
                _storyIDIndex = 0;
                _progressText = ""
                _IDs = []
                
                return
            }
            
            JKSQLiteManager.cacheDetailWithIDs(IDs: IDs)
            
        }, extraBlock: nil)
    }

    /** 缓存新闻详情数据到本地 */
    public class func cacheDetail(detailDict: [String : AnyObject]?, ID: String?, justCache: Bool) {
        
        if detailDict == nil {
            return
        }
        
        if (justCache) { // 仅仅是缓存的话，提前下载图片
            
            if let imgUrl = detailDict!["image"] as? String {
                
                if let url = URL(string: imgUrl) {
                    
                    KingfisherManager.shared.downloader.downloadImage(with: url, retrieveImageTask: nil, options: KingfisherManager.shared.defaultOptions, progressBlock: nil, completionHandler: nil)
                }
            }
            
            if let cssArr = detailDict!["css"] as? [String] {
                
                // 缓存css
                cacheStoryDetailCss(ID: String(format: "%@", detailDict!["id"] as! CVarArg), cssUrlStr: cssArr.first)
            }
        }
        
        if queryStoryDetailWithID(ID: ID) != nil { return }

        guard let storyID = ID else { return }
        guard let title = detailDict!["title"] else { return }
        
        // 将字典转为NSData
        guard let detailData = try? JSONSerialization.data(withJSONObject: detailDict!, options: JSONSerialization.WritingOptions.prettyPrinted) else {
            return
        }
        
        // 将NSData转为字符串
        guard let storyDetail = String(data: detailData, encoding: String.Encoding.utf8) else { return }

        // 拼接插入SQL语句
        let insertSQL = "INSERT INTO t_story (storyID, title, isCollected, isLiked, storyDetail) VALUES (?, ?, ?, ?, ?);"
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
            
            db.executeUpdate(insertSQL, withArgumentsIn: [storyID, title, "0", "0", storyDetail])
        })
    }
    
    // MARK: - ------------------删------------------
    
    /** 删除顶部topStories */
    public class func deleteTopStories() {
        
        let deleteSQL = "delete from t_storyList where isTop = '1';"
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
            
            db.executeUpdate(deleteSQL, withArgumentsIn: [])
        })
    }

    /** 清理缓存的数据 */
    public class func clearData() {
        
        // 1.获取一周前的时间
        let aWeekAgoDate = Date(timeIntervalSinceNow: -7 * 24 * 60 * 60)
        let fmt = DateFormatter()
        fmt.dateFormat = "yyyy-MM-dd HH:mm:ss" // 2016-08-26 12:25:11
        let dateString = fmt.string(from: aWeekAgoDate)
        
        
        // 从数据库获取收藏列表
        let querySQL = "SELECT * FROM t_storyList WHERE isCollected = '1' and isTop = '0' order by updateTime desc;"
        
        var dateArr = [String]()
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
            
            guard let result = db.executeQuery(querySQL, withArgumentsIn: []) else { return }
            
            while result.next() {
                
                guard let dateStr = result.string(forColumn: "date") else { continue }
                
                if dateArr.contains(dateStr) { continue }
                
                dateArr.append(dateStr)
                
                return
            }
        })
        
        try? FileManager.default.removeItem(atPath: JKCssDirectory)
        
        if (dateArr.count >= 1) { // 当天有收藏文章的，当天所有文章都不清理，以防从本地获取数据时只能获取收藏的那几条
            
            for dateStr in dateArr {
                
                // 2.拼接删除数据的SQL
                let deleteSQL = "DELETE FROM t_story WHERE createTime < '" + dateString + "';"
                let deleteSQL2 = "DELETE FROM t_storyList WHERE createTime < '" + dateString + "' and date != '" + dateStr + "';"
                
                
                // 3.执行SQL语句
                JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
                    
                    db.executeUpdate(deleteSQL, withArgumentsIn: [])
                    db.executeUpdate(deleteSQL2, withArgumentsIn: [])
                })
            }
            
        }else{ // 没有收藏任何文章的，直接清理
            
            // 2.拼接删除数据的SQL
            let deleteSQL = "DELETE FROM t_story WHERE createTime < '" + dateString + "';"
            let deleteSQL2 = "DELETE FROM t_storyList WHERE createTime < '" + dateString + "';"
            
            
            // 3.执行SQL语句
            JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
                
                db.executeUpdate(deleteSQL, withArgumentsIn: [])
                db.executeUpdate(deleteSQL2, withArgumentsIn: [])
            })
        }
    }
    
    // MARK: - ------------------改------------------
    
    /** 更新文章为非latest */
    public class func updateNotLatestWithStoryID(storyID: String) {
        
        // 拼接SQL语句
        let updateSQL = "update t_storyList set isLatest = '0' where storyID = '" + storyID + "';"
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
            
            db.executeUpdate(updateSQL, withArgumentsIn: [])
        })
    }
    
    /** 更新文章为是否已赞状态 */
    public class func updateIsLikedWithStoryID(storyID: String?, isLiked: Bool) {
        
        if storyID == nil { return }
        
        // 拼接SQL语句
        let updateSQL = "update t_story set isLiked = '" + (isLiked ? "1" : "0") + "' where storyID = " + storyID! + ";"
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
            
            db.executeUpdate(updateSQL, withArgumentsIn: [])
        })
    }

    /** 更新文章为已读状态 */
    public class func updateReadStatusWithStoryID(storyID: String?) {
        
        if storyID == nil { return }
        
        if queryIsReadWithStoryID(storyID: storyID) { return }
        
        var alreadyReadDict = UserDefaults.standard.object(forKey: JKAlreadyReadDictKey) as? [String : Bool]
        
        if (alreadyReadDict == nil) {
            alreadyReadDict = [String : Bool]()
        }
        
        alreadyReadDict![storyID!] = true
        
        UserDefaults.standard.set(alreadyReadDict, forKey: JKAlreadyReadDictKey)
        UserDefaults.standard.synchronize()
        
        // 拼接SQL语句
        let updateSQL = "update t_storyList set isRead = '1' where storyID = '" + storyID! + "';"
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
            
            db.executeUpdate(updateSQL, withArgumentsIn: [])
        })
    }

    /** 更新所有文章为未收藏状态 */
    public class func updateCollectionsCancelWithStoryIDs(storyIDs: [String]?) {
        
        if storyIDs == nil {
            return
        }
        
        for i in 0 ..< storyIDs!.count {
            
            let _ = updateCollectedStatusWithStoryID(storyID: storyIDs![i], collected: false)
        }
    }

    /** 更新文章为收藏\未收藏状态 */
    public class func updateCollectedStatusWithStoryID(storyID: String?, collected: Bool) -> Bool {
        
        // 拼接SQL语句
        let updateSQL1 = "update t_storyList set isCollected = " + (collected ? "'1'" : "'0'") + ", updateTime = '" + JKDateTool.getDateAndTimeStrOfToday() + "' where storyID = '" + storyID! + "' and isTop = '0';"
        
        let updateSQL2 = "update t_story set isCollected = " + (collected ? "'1'" : "'0'") + " where storyID = '" + storyID! + "';"
        
        var success = false
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
            
            db.executeUpdate(updateSQL1, withArgumentsIn: [])
            success = db.executeUpdate(updateSQL2, withArgumentsIn: [])
        })
        
        return success
    }
    
    // MARK: - ------------------查------------------
    
    /** 查询文章是否已赞 */
    public class func queryIsLikedWithStoryID(storyID: String?) -> Bool {
        
        if storyID == nil {
            return false
        }
        
        // 从数据库获取收藏列表
        let querySQL = "SELECT * FROM t_story WHERE storyID = '" + storyID! + "';"
        
        var isLiked = false
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
            
            guard let result = db.executeQuery(querySQL, withArgumentsIn: []) else { return }
            
            while result.next() {
                
                guard let like = result.string(forColumn: "isLiked") else { continue }
                
                isLiked = (like == "1")
            }
        })
        
        return isLiked
    }

    /** 获取收藏列表 */
    public class func queryCollectedStoryList() -> [[String: AnyObject]] {
        
        // 从数据库获取收藏列表
        let querySQL = "SELECT * FROM t_storyList WHERE isCollected = '1' and isTop = '0' order by updateTime desc;"
        
        var lists = [[String: AnyObject]]()
        var storyIDs = [String]()
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
            
            guard let result = db.executeQuery(querySQL, withArgumentsIn: []) else { return }
            
            while result.next() {
                
                guard let list = result.string(forColumn: "list") else { continue }
                guard let storyID = result.string(forColumn: "storyID") else { continue }
                
                guard let listData = list.data(using: String.Encoding.utf8) else { continue }
                
                guard let listDict = try? JSONSerialization.jsonObject(with: listData, options: JSONSerialization.ReadingOptions.mutableContainers) else { continue }
                
                guard let dict = listDict as? [String : AnyObject] else { continue }
                
                if storyIDs.contains(storyID) { continue }
                
                storyIDs.append(storyID)
                
                lists.append(dict)
            }
        })
        
        storyIDs.removeAll()
        
        return lists
    }

    /** 获取首页topStories的storyID列表 */
    public class func queryTopStoryIDs() -> [String] {
        
        let querySQL = "SELECT * FROM t_storyList WHERE isTop = '1' order by storyID;"
        
        var storyIDs = [String]()
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
          
            guard let result = db.executeQuery(querySQL, withArgumentsIn: []) else { return }
            
            while result.next() {
                
                guard let id = result.string(forColumn: "storyID") else { continue }
                
                storyIDs.append(id)
            }
        })
        
        return storyIDs
    }

    /** 获取首页最新数据 */
    public class func queryLatestStoryList() -> [String: AnyObject]? {
        
        let today = JKDateTool.getDateOfToday()
        
        // 从数据库获取详情数据
        let queryTodaySQL = "SELECT * FROM t_storyList WHERE date = '" + today + "' order by ID desc;"
        let queryYesterdaySQL = "SELECT * FROM t_storyList WHERE date = '" + JKDateTool.getPreviousDayOfSpecifiedDate(date: today) + "' order by ID desc;"
        
        var lists = [[String: AnyObject]]()
        var topArr = [[String: AnyObject]]()
        
        var dateStr: String? = nil
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
            
            guard let result = db.executeQuery(queryTodaySQL, withArgumentsIn: []) else { return }
            
            while result.next() {
                
                guard let list = result.string(forColumn: "list") else { continue }
                guard let top = result.string(forColumn: "isTop") else { continue }
                
                if (dateStr == nil) {
                    
                    guard let date = result.string(forColumn: "date") else { continue }
                    dateStr = date
                }
                
                guard let listData = list.data(using: String.Encoding.utf8) else { continue }
                
                guard let listDict = try? JSONSerialization.jsonObject(with: listData, options: JSONSerialization.ReadingOptions.mutableContainers) else { continue }
                
                guard let dict = listDict as? [String : AnyObject] else { continue }
                
                if top == "1" {
                    
                    topArr.append(dict)
                    
                }else {
                    
                    lists.append(dict)
                }
            }
            
            if dateStr == nil || lists.count <= 0 || topArr.count <= 0 {
                
                lists.removeAll()
                topArr.removeAll()
                
                dateStr = nil
                
                guard let result2 = db.executeQuery(queryYesterdaySQL, withArgumentsIn: []) else { return }
                
                while result2.next() {
                    
                    guard let list = result.string(forColumn: "list") else { continue }
                    guard let top = result.string(forColumn: "isTop") else { continue }
                    
                    if (dateStr == nil) {
                        
                        guard let date = result.string(forColumn: "date") else { continue }
                        dateStr = date
                    }
                    
                    guard let listData = list.data(using: String.Encoding.utf8) else { continue }
                    
                    guard let listDict = try? JSONSerialization.jsonObject(with: listData, options: JSONSerialization.ReadingOptions.mutableContainers) else { continue }
                    
                    guard let dict = listDict as? [String : AnyObject] else { continue }
                    
                    if top == "1" {
                        
                        topArr.append(dict)
                        
                    }else {
                        
                        lists.append(dict)
                    }
                }
            }
        })
        
        if (dateStr == nil || lists.count <= 0 || topArr.count <= 0) { return nil }
        
        return [JKStoryDateKey : dateStr! as AnyObject, JKStoryListDictKey : lists as AnyObject, JKTopStoryListKey : topArr as AnyObject]
    }

    /** 指定日期的story列表 */
    public class func queryStoryListWithDate(date: String?) -> [String : AnyObject]? {
        
        if date == nil {
            return nil
        }
        
        // 从数据库获取指定日期的story列表
        let querySQL = "SELECT * FROM t_storyList WHERE date = '" + date! + "' and isTop = '0' and isLatest = '0' order by ID desc;"
        
        var lists = [[String : AnyObject]]()
        
        var dateStr: String? = nil
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
            
            guard let result = db.executeQuery(querySQL, withArgumentsIn: []) else { return }
            
            while result.next() {
                
                guard let list = result.string(forColumn: "list") else { continue }
                
                if (dateStr == nil) {
                    
                    guard let date = result.string(forColumn: "date") else { continue }
                    dateStr = date
                }
                
                guard let listData = list.data(using: String.Encoding.utf8) else { continue }
                
                guard let listDict = try? JSONSerialization.jsonObject(with: listData, options: JSONSerialization.ReadingOptions.mutableContainers) else { continue }
                
                guard let dict = listDict as? [String : AnyObject] else { continue }
                
                lists.append(dict)
            }
        })
        
        if (lists.count <= 0 || dateStr == nil) { return nil }
        
        return [JKStoryDateKey : dateStr as AnyObject, JKStoryListDictKey : lists as AnyObject]
    }

    /** 从数据库获取详情数据 */
    public class func queryStoryDetailWithID(ID: String?) -> [String : AnyObject]? {
        
        if ID == nil {
            return nil
        }
        
        let querySQL = "SELECT * FROM t_story WHERE storyID = '" + ID! + "';"
        
        var dict: [String : AnyObject]? = nil
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
            
            guard let result = db.executeQuery(querySQL, withArgumentsIn: []) else { return }
            
            while result.next() {
                
                guard let storyDetail = result.string(forColumn: "storyDetail") else { continue }
                
                guard let detailData = storyDetail.data(using: String.Encoding.utf8) else { continue }
                
                guard let dataDict = try? JSONSerialization.jsonObject(with: detailData, options: JSONSerialization.ReadingOptions.mutableContainers) else { continue }
                
                dict = dataDict as? [String : AnyObject]
            }
        })
        
        return dict
    }

    /** 查询文章是否已读 */
    public class func queryIsReadWithStoryID(storyID: String?) -> Bool {
        
        if storyID == nil {
            return false
        }
        
        let alreadyReadDict = UserDefaults.standard.object(forKey: JKAlreadyReadDictKey) as? [String : Bool]
        
        if (alreadyReadDict == nil) {
            
            return false
        }
        
        let isRead = alreadyReadDict![storyID!]
        
        /* 从数据库获取详情数据
        let querySQL = "SELECT * FROM t_storyList WHERE storyID = '" + storyID! + "';"
        
        var isRead = false
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
         
            guard let result = db.executeQuery(querySQL, withArgumentsIn: []) else { return }
         
            while result.next() {
         
                guard let read = result.string(forColumn: "isRead") else { continue }
         
                isRead = (isRead == "1")
            }
        }) */
        
        return isRead ?? false
    }

    /** 查询文章是否已收藏 */
    public class func queryIsCollectedWithStoryID(storyID: String?) -> Bool {
        
        if storyID == nil {
            return false
        }
        
        // 从数据库获取详情数据
        let querySQL = "SELECT * FROM t_story WHERE storyID = '" + storyID! + "';"
        
        var isCollected = false
        
        JKSQLiteManager.shared.dbQueue?.inDatabase({ (db) in
            
            guard let result = db.executeQuery(querySQL, withArgumentsIn: []) else { return }
            
            while result.next() {
                
                guard let collected = result.string(forColumn: "isCollected") else { continue }
                
                isCollected = (collected == "1")
            }
        })
        
        return isCollected
    }
    
}
